import csv
import sqlite3

import requests
from langchain_chroma import Chroma
from langchain_community.utilities import SQLDatabase
from langchain_core.tools import tool
# from utils import BGE_Embed


def _get_city_geocode(city_name):
    city_dict = {}
    with open("./weather_district_id.csv", "r", encoding="utf-8") as f:
        csv_reader = csv.DictReader(f)
        # 构建一个城市名称：城市ID的对应字典
        for row in csv_reader:
            city_geocode = row['city_geocode'].strip()
            district = row['district'].strip()
            if district not in city_dict:
                city_dict[district] = city_geocode
    return city_dict[city_name]


@tool("get_weather")  # 工具的名称（注意这是必写内容）
def get_weather(location: str) -> str:
    """ 天气查询 """  # 工具的描述（注意这是必写内容）
    print("天气查询中.......")
    city_geocode = _get_city_geocode(location)
    response = requests.get(
        url=f"https://api.map.baidu.com/weather/v1/?district_id={city_geocode}&data_type=now&ak=BOZyKLvOGW0bxcVBCdXOTCnv9oMHjv8o")
    data = response.json()

    text = data["result"]["now"]["text"]
    temp = data["result"]["now"]["temp"]
    feels_like = data["result"]["now"]["feels_like"]
    rh = data["result"]["now"]["rh"]
    wind_dir = data["result"]["now"]["wind_dir"]
    wind_class = data["result"]["now"]["wind_class"]

    return (f"位置：{location} 当前天气：{text},温度：{temp}℃,体感温度：{feels_like}℃,相对湿度：{rh}%,"
            f"{wind_dir}:{wind_class}")


@tool("get_goods")
def get_goods(name: str) -> str:
    """ 查询商品信息 """
    print("查询商品中....")
    message = ""
    conn = sqlite3.connect("factory.db")
    cursor = conn.cursor()
    cursor = cursor.execute('SELECT id, name, price, number  from GOODS WHERE name=?', [name])
    for row in cursor:
        message += f'"ID": {row[0]}, "NAME": {row[1]}, "PRICE": {row[2]}, "NUMBER": {row[3]} \n'

    if message == "":
        message = "你所指定的商品，未查到"
    conn.commit()
    conn.close()

    return message


import xlwt


@tool("save_goods")
def save_goods(id: int, name: str, price: float, number: int):
    """ 保存查询的商品信息到excel中 """
    print("保存商品信息中....")
    workbook = xlwt.Workbook(encoding="utf-8")
    worksheet = workbook.add_sheet("商品数据")
    worksheet.write(0, 0, "商品编号")
    worksheet.write(0, 1, "商品名字")
    worksheet.write(0, 2, "商品价格")
    worksheet.write(0, 3, "商品数量")
    worksheet.write(1, 0, id)
    worksheet.write(1, 1, name)
    worksheet.write(1, 2, price)
    worksheet.write(1, 3, number)
    workbook.save("goods.xls")

    return "商品信息已保存至./goods.xls"


# @tool("rag_search")
# def rag_search(query: str):
#     """ 检索三国演义书籍的上下文 """
#     print("检索《三国演义》中 ...")
#     embedding = BGE_Embed()
#     vector_db = Chroma(
#         embedding_function=embedding,
#         persist_directory='../LangChain/data/chroma_db'
#     )
#     context = vector_db.similarity_search_with_score(query)  # 针对内容进行检索
#     return context[0][0].__dict__['page-content']
